﻿ IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_BrowseUsers')
	BEGIN
		DROP  Procedure  [esf_sso].P_BrowseUsers
	END

GO

CREATE Procedure [esf_sso].P_BrowseUsers
	@pID					BIGINT = NULL
	,@pAlias				VARCHAR (20) = NULL
	,@pFirstName			VARCHAR (40) = NULL
	,@pSecondName			VARCHAR (40) = NULL
	,@pSurName				VARCHAR (40) = NULL
	,@pLastName				VARCHAR (40) = NULL
	,@pFullName				VARCHAR (166) = NULL
	,@pGenderID				BIGINT = NULL
	,@pIdentificationTypeID	BIGINT = NULL
	,@pIdentificationNumber	VARCHAR (20) = NULL
	,@pEmail				VARCHAR (162) = NULL
	,@pIsEnabled			BIT = NULL
	,@pEnterpriseID			BIGINT = NULL
	,@pEasySearch			BIT = 0
	,@pEnterpriseName		VARCHAR (MAX) = NULL
	,@pPosition				VARCHAR (MAX) = NULL
	,@pProfession			VARCHAR (MAX) = NULL
	,@pUserGroupID			INT = NULL

	,@pPageIndex			BIGINT = 0
	,@pPageSize				BIGINT = 30

	,@pExtraValue			VARCHAR (MAX) = NULL
AS

	IF @pEasySearch = 0
	BEGIN

		SELECT * FROM
			(SELECT
				ROW_NUMBER () OVER (ORDER BY VU.Usr_Per_cal_FullName) AS Ri
				,COUNT(*) OVER () AS RCount				
				,VU.Usr_Alias
				,VU.Usr_IsEnabled
				,VU.Usr_CreationDateTime
				,VU.Usr_Per_ID
				,VU.Usr_Per_FirstName
				,VU.Usr_Per_SecondName
				,VU.Usr_Per_SurName
				,VU.Usr_Per_LastName
				,VU.Usr_Per_IdentificationNumber
				,VU.Usr_Per_Email
				,VU.Usr_Per_IT_Code
			FROM
				[esf_sso].VUser VU
			LEFT JOIN
				esf_sso.User_BasicReference UB ON VU.Usr_Per_ID = UB.UserPersonID
			LEFT JOIN
				esf_sso.VBasicReference VUG ON 
					VUG.ID = UB.BasicReferenceID 
					-- 5: UserGroup
					AND VUG.BasicReferenceTypeID = 5
			WHERE
				(@pID IS NULL OR VU.Usr_Per_ID = @pID)
				AND (@pAlias IS NULL OR VU.Usr_Alias LIKE @pAlias)
				AND (@pFirstName IS NULL OR VU.Usr_Per_FirstName LIKE @pFirstName)
				AND (@pSecondName IS NULL OR VU.Usr_Per_SecondName LIKE @pSecondName)
				AND (@pSurName IS NULL OR VU.Usr_Per_SurName LIKE @pSurName)
				AND (@pLastName IS NULL OR VU.Usr_Per_LastName LIKE @pLastName)
				AND (@pGenderID IS NULL OR VU.Usr_Per_Gen_ID = @pGenderID)
				AND (@pIdentificationTypeID IS NULL OR VU.Usr_Per_IT_ID = @pIdentificationTypeID)
				AND (@pEnterpriseID IS NULL OR VU.Usr_Per_Ent_ID = @pEnterpriseID)
				AND (@pIsEnabled IS NULL OR VU.Usr_IsEnabled = @pIsEnabled)
				AND (@pFullName IS NULL OR VU.Usr_Per_cal_FullName LIKE @pFullName)
				AND (@pIdentificationNumber IS NULL OR VU.Usr_Per_IdentificationNumber LIKE @pIdentificationNumber)
				AND (@pEmail IS NULL OR VU.Usr_Per_Email LIKE @pEmail)
				AND (@pEnterpriseName IS NULL OR VU.Usr_Per_Ent_Name LIKE @pEnterpriseName)
				AND (@pPosition IS NULL OR VU.Usr_Per_Position LIKE @pPosition)
				AND (@pProfession IS NULL OR VU.Usr_Per_Profession LIKE @pProfession)
				AND (@pUserGroupID IS NULL OR VUG.ID = @pUserGroupID)
				-- Ajustado por cliente, BasicReferenceTypeID = 53-Codigos Institucionales
				AND (@pExtraValue IS NULL OR @pExtraValue = (SELECT TOP 1 UBR.BasicReferenceID FROM esf_sso.User_BasicReference UBR INNER JOIN esf_sso.BasicReference BR ON BR.ID = BasicReferenceID WHERE UBR.UserPersonID = VU.Usr_Per_ID AND BR.BasicReferenceTypeID = 53))
			GROUP BY
				VU.Usr_Per_cal_FullName
				,VU.Usr_Alias
				,VU.Usr_IsEnabled
				,VU.Usr_CreationDateTime
				,VU.Usr_Per_ID
				,VU.Usr_Per_FirstName
				,VU.Usr_Per_SecondName
				,VU.Usr_Per_SurName
				,VU.Usr_Per_LastName
				,VU.Usr_Per_IdentificationNumber
				,VU.Usr_Per_Email
				,VU.Usr_Per_IT_Code
			) T
		WHERE
			T.Ri BETWEEN (@pPageIndex * @pPageSize + 1) AND (@pPageIndex + 1) * @pPageSize
		ORDER BY 
			T.Ri

	END
	ELSE
	BEGIN

		SELECT * FROM
			(SELECT
				ROW_NUMBER () OVER (ORDER BY VU.Usr_Per_cal_FullName) AS Ri
				,COUNT(*) OVER () AS RCount
				,VU.*
			FROM
				[esf_sso].VUser VU
			WHERE
				(@pFullName IS NULL OR VU.Usr_Per_cal_FullName LIKE @pFullName)
				OR (@pIdentificationNumber IS NULL OR VU.Usr_Per_IdentificationNumber LIKE @pIdentificationNumber)
				OR (@pEmail IS NULL OR VU.Usr_Per_Email LIKE @pEmail)
				AND (@pIsEnabled IS NULL OR VU.Usr_IsEnabled = @pIsEnabled)
				-- Ajustado por cliente, BasicReferenceTypeID = 53-Codigos Institucionales
				AND (@pExtraValue IS NULL OR @pExtraValue = (SELECT TOP 1 UBR.BasicReferenceID FROM esf_sso.User_BasicReference UBR INNER JOIN esf_sso.BasicReference BR ON BR.ID = BasicReferenceID WHERE UBR.UserPersonID = VU.Usr_Per_ID AND BR.BasicReferenceTypeID = 53))
			) T
		WHERE
			T.Ri BETWEEN (@pPageIndex * @pPageSize + 1) AND (@pPageIndex + 1) * @pPageSize
		ORDER BY 
			T.Ri

	END

GO